import com.cnsugar.common.jdbc.DbUtils;
import org.apache.commons.io.FileUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @name GenerateHtml
 * @description 生成数据库表结构说明文档
 * @Author Star
 * @Version 2014-12-29下午2:09:54
 **/

public class GenerateHtml {
	public static boolean generate(String html) throws Exception {
		return generate(html, null);
	}

	public static boolean generate(String html, String[] skip) throws Exception {
		JdbcTemplate jt = DbUtils.getJdbcTemplate();
		String owner = null;
		try {
			owner = jt.getDataSource().getConnection().getMetaData().getUserName();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String sql = "SELECT A.TABLE_NAME, B.COMMENTS, A.TABLESPACE_NAME FROM USER_TABLES A LEFT JOIN USER_TAB_COMMENTS B ON A.TABLE_NAME=B.TABLE_NAME WHERE A.TABLE_NAME NOT LIKE 'GEN_%' ORDER BY A.TABLE_NAME";
		List<Map<String, Object>> list = DbUtils.queryForMapList(sql, jt);
		List<String> lines = new ArrayList<>();
		StringBuffer body = new StringBuffer("<html><head><meta charset=\"utf-8\"><style>");
		body.append("body {font-family: \"Helvetica Neue\", Helvetica, \"Hiragino Sans GB\", Arial, sans-serif; font-size: 13px; line-height: 18px; color: #737373; background-color: white; margin: 10px 13px 10px 13px;}");
		body.append("table {margin: 10px 0 15px 0; border-collapse: collapse;}");
		body.append("td,th {border: 1px solid #ddd; padding: 3px 10px;}");
		body.append("th {padding: 5px 10px;}");
		body.append("a {color: #0069d6;}");
		body.append("a:hover {color: #0050a3; text-decoration: none;}");
		body.append("h3 {color: #404040; line-height: 36px; font-size: 18px; margin: 0; padding: 0;}");
		body.append("</style></head><body>");
		body.append("<h3>目录</h3><ul>");
		int i = 1;
		for (Map<String, Object> map : list) {
			String table = (String) map.get("TABLE_NAME");
			if (skip != null) {
				boolean ignore = false;
				for (String t : skip) {
					if (table.equalsIgnoreCase(t)) {
						ignore = true;
						break;
					}
				}
				if (ignore) {
					continue;
				}
			}
			body.append("<li>").append("<a href=\"#").append(i).append("\">").append(i).append(". ")
					.append(map.get("COMMENTS")).append("(").append(table).append(")</a></li>");
			i++;
		}
		body.append("</ul><br /><br />");
		lines.add(body.toString());
		i = 1;
		for (Map<String, Object> map : list) {
			String table = (String) map.get("TABLE_NAME");
			if (skip != null) {
				boolean ignore = false;
				for (String t : skip) {
					if (table.equalsIgnoreCase(t)) {
						ignore = true;
						break;
					}
				}
				if (ignore) {
					continue;
				}
			}
			System.out.println(">>"+table);
			String colsql = "SELECT A.COLUMN_NAME,A.DATA_TYPE,A.DATA_LENGTH,A.DATA_PRECISION,A.DATA_SCALE,A.DATA_DEFAULT, A.NULLABLE,B.COMMENTS,"
					+ "(SELECT DISTINCT 1 FROM USER_CONS_COLUMNS C, USER_CONSTRAINTS D WHERE C.COLUMN_NAME=A.COLUMN_NAME" +
					" AND C.TABLE_NAME=A.TABLE_NAME AND D.CONSTRAINT_NAME=C.CONSTRAINT_NAME AND D.TABLE_NAME=C.TABLE_NAME" +
					" AND D.CONSTRAINT_TYPE='P') AS PK"
					+ " FROM USER_TAB_COLUMNS A LEFT JOIN USER_COL_COMMENTS B"
					+ " ON (B.COLUMN_NAME=A.COLUMN_NAME AND B.TABLE_NAME=A.TABLE_NAME)"
					+ " WHERE A.TABLE_NAME=UPPER('" + table + "') ORDER BY A.COLUMN_ID";
			StringBuffer sb = new StringBuffer();
			sb.append("<br>").append("<h3><a name=\"").append(i).append("\"></a>").append(i).append(". ")
					.append(map.get("COMMENTS")).append("(").append(table).append(")</h3>");
			sb.append("<table width='75%'>");
			sb.append("<tr>");
			sb.append("<th width='20%'>字段名</th>");
			sb.append("<th width='15%'>类型</th>");
			sb.append("<th width='12%'>是否可空</th>");
			sb.append("<th width='20%'>默认值</th>");
			sb.append("<th>说明</th>");
			sb.append("</tr>");
			List<Map<String, Object>> ll = DbUtils.queryForMapList(colsql);
			for (Map<String, Object> m : ll) {
				sb.append("<tr>");
				sb.append("<td>").append(getString(m.get("COLUMN_NAME"))).append("</td>");
				sb.append("<td>").append(getString(m.get("DATA_TYPE"))).append("(").append(getString(m.get("DATA_LENGTH"))).append(")</td>");
				sb.append("<td align=\"center\">").append(getString(m.get("NULLABLE"))).append("</td>");
				sb.append("<td>").append(getString(m.get("DATA_DEFAULT"))).append("</td>");
				sb.append("<td>");
				String s = getString(m.get("COMMENTS"));
				if (s.equals("")) {
					if ("1".equals(getString(m.get("PK")))) {
						sb.append("主键");
					}
				} else {
					sb.append(s);
				}
				sb.append("</td></tr>");
			}
			sb.append("</table>");
			lines.add(sb.toString());
			i++;
		}
		lines.add("</body></html>");

		File file = new File(html);
		if (file.exists()) {
			file.delete();
		}
		file.createNewFile();
		FileUtils.writeLines(file, lines);
		System.out.println("ok, total " + (i - 1) + " tables, file:" + html);
		return true;
	}

	private static String getString(Object obj) {
		if (obj == null || obj.toString().equals("\"\"")) {
			return "";
		}
		return obj.toString();
	}

	public static void main(String[] args) throws Exception {
		generate("E:\\文档\\管综平台\\2.0\\数据库\\数据库说明文档.html");
	}
}
